package com.insight.service;

import com.insight.common.enums.ChangeType;
import com.insight.entity.MetadataColumn;
import com.insight.entity.MetadataIndex;
import com.insight.entity.MetadataTable;
import com.insight.entity.MetadataVersion;
import com.insight.repository.MetadataColumnRepository;
import com.insight.repository.MetadataIndexRepository;
import com.insight.repository.MetadataVersionRepository;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.nio.charset.StandardCharsets;
import java.security.MessageDigest;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * T072: 版本管理服务实现
 * 负责元数据版本追踪、SHA-256哈希计算、Jaccard相似度检测
 *
 * @author Insight Team
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class VersionManagementService {

    private final MetadataVersionRepository versionRepository;
    private final MetadataColumnRepository columnRepository;
    private final MetadataIndexRepository indexRepository;
    private final ObjectMapper objectMapper = new ObjectMapper();

    /**
     * 创建表的新版本快照
     *
     * @param table      表
     * @param changeType 变更类型
     * @param createdBy  创建者
     * @return 版本记录
     */
    @Transactional
    public MetadataVersion createVersion(MetadataTable table, ChangeType changeType, String createdBy) {
        // 参数校验
        validateParameters(table, changeType);

        log.info("创建版本快照: table={}, changeType={}", table.getFullName(), changeType);

        // 1. 获取当前表的列和索引
        List<MetadataColumn> columns = columnRepository.findByTable(table);
        List<MetadataIndex> indexes = indexRepository.findByTable(table);

        // 2. 计算结构哈希(SHA-256)
        String structureHash = calculateStructureHash(columns, indexes);
        log.debug("结构哈希: table={}, hash={}", table.getFullName(), structureHash);

        // 3. 生成版本快照(JSONB格式)
        String versionSnapshot = generateVersionSnapshot(table, columns, indexes);

        // 4. 获取下一个版本号
        Integer maxVersion = versionRepository.findMaxVersionNumberByTable(table);
        Integer nextVersion = (maxVersion == null ? 0 : maxVersion) + 1;

        // 5. 创建版本记录
        MetadataVersion version = new MetadataVersion();
        version.setTable(table);
        version.setVersionNumber(nextVersion);
        version.setChangeType(changeType);

        // 将版本快照转换为Map(MetadataVersion使用snapshotData字段,类型为Map)
        Map<String, Object> snapshotMap = parseSnapshotToMap(versionSnapshot);
        // 在Map中添加structureHash
        snapshotMap.put("structureHash", structureHash);
        version.setSnapshotData(snapshotMap);

        version.setCreatedBy(createdBy);
        version.setCreatedAt(LocalDateTime.now());

        // 6. 保存版本
        MetadataVersion savedVersion = versionRepository.save(version);
        log.info("版本创建成功: table={}, version={}, hash={}",
                table.getFullName(), nextVersion, structureHash);

        return savedVersion;
    }

    /**
     * 计算表结构的SHA-256哈希值
     * 用于快速判断表结构是否变化
     *
     * @param columns 列列表
     * @param indexes 索引列表
     * @return SHA-256哈希值(64位十六进制字符串)
     */
    public String calculateStructureHash(List<MetadataColumn> columns, List<MetadataIndex> indexes) {
        try {
            // 1. 构建结构字符串(按顺序拼接列和索引信息)
            StringBuilder structure = new StringBuilder();

            // 1.1 添加列信息(按序号排序)
            columns.stream()
                    .sorted(Comparator.comparing(MetadataColumn::getColumnPosition))
                    .forEach(col -> {
                        structure.append("COL:")
                                .append(col.getColumnName()).append("|")
                                .append(col.getDataType()).append("|")
                                .append(col.getNullable()).append("|")
                                .append(col.getIsPrimaryKey()).append("|")
                                .append(col.getColumnPosition())
                                .append(";");
                    });

            // 1.2 添加索引信息(按索引名排序)
            indexes.stream()
                    .sorted(Comparator.comparing(MetadataIndex::getIndexName))
                    .forEach(idx -> {
                        structure.append("IDX:")
                                .append(idx.getIndexName()).append("|")
                                .append(idx.getIndexType()).append("|")
                                .append(idx.getColumnNames()).append("|")
                                .append(idx.getIsUnique())
                                .append(";");
                    });

            // 2. 计算SHA-256哈希
            MessageDigest digest = MessageDigest.getInstance("SHA-256");
            byte[] hash = digest.digest(structure.toString().getBytes(StandardCharsets.UTF_8));

            // 3. 转换为十六进制字符串
            StringBuilder hexString = new StringBuilder();
            for (byte b : hash) {
                String hex = Integer.toHexString(0xff & b);
                if (hex.length() == 1) {
                    hexString.append('0');
                }
                hexString.append(hex);
            }

            return hexString.toString();

        } catch (Exception e) {
            log.error("计算结构哈希失败: {}", e.getMessage(), e);
            throw new RuntimeException("计算结构哈希失败", e);
        }
    }

    /**
     * 计算两个表的Jaccard相似度
     * 用于检测表重命名(相似度>80%认为是重命名)
     *
     * @param table1 表1
     * @param table2 表2
     * @return Jaccard相似度(0.0-1.0)
     */
    public double calculateJaccardSimilarity(MetadataTable table1, MetadataTable table2) {
        // 1. 获取两个表的列和索引
        List<MetadataColumn> columns1 = columnRepository.findByTable(table1);
        List<MetadataIndex> indexes1 = indexRepository.findByTable(table1);
        List<MetadataColumn> columns2 = columnRepository.findByTable(table2);
        List<MetadataIndex> indexes2 = indexRepository.findByTable(table2);

        // 2. 提取特征集合(列名+数据类型,索引名)
        Set<String> features1 = extractFeatures(columns1, indexes1);
        Set<String> features2 = extractFeatures(columns2, indexes2);

        // 3. 计算交集和并集
        Set<String> intersection = new HashSet<>(features1);
        intersection.retainAll(features2);

        Set<String> union = new HashSet<>(features1);
        union.addAll(features2);

        // 4. 计算Jaccard相似度 = |交集| / |并集|
        if (union.isEmpty()) {
            return 0.0;
        }

        double similarity = (double) intersection.size() / union.size();
        log.debug("Jaccard相似度: table1={}, table2={}, similarity={}",
                table1.getFullName(), table2.getFullName(), similarity);

        return similarity;
    }

    /**
     * 比较两个版本的差异
     *
     * @param oldVersion 旧版本
     * @param newVersion 新版本
     * @return 差异描述
     */
    public String compareVersions(MetadataVersion oldVersion, MetadataVersion newVersion) {
        try {
            // 1. 获取版本快照(MetadataVersion使用snapshotData字段,类型为Map)
            Map<String, Object> oldSnapshot = oldVersion.getSnapshotData();
            Map<String, Object> newSnapshot = newVersion.getSnapshotData();

            StringBuilder diff = new StringBuilder();
            diff.append("版本差异: ").append(oldVersion.getVersionNumber())
                    .append(" -> ").append(newVersion.getVersionNumber()).append("\n");

            // 2. 比较列差异
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> oldColumns = (List<Map<String, Object>>) oldSnapshot.get("columns");
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> newColumns = (List<Map<String, Object>>) newSnapshot.get("columns");

            Set<String> oldColumnNames = oldColumns.stream()
                    .map(col -> (String) col.get("columnName"))
                    .collect(Collectors.toSet());
            Set<String> newColumnNames = newColumns.stream()
                    .map(col -> (String) col.get("columnName"))
                    .collect(Collectors.toSet());

            // 2.1 新增的列
            Set<String> addedColumns = new HashSet<>(newColumnNames);
            addedColumns.removeAll(oldColumnNames);
            if (!addedColumns.isEmpty()) {
                diff.append("新增列: ").append(String.join(", ", addedColumns)).append(" [ADDED]\n");
            }

            // 2.2 删除的列
            Set<String> removedColumns = new HashSet<>(oldColumnNames);
            removedColumns.removeAll(newColumnNames);
            if (!removedColumns.isEmpty()) {
                diff.append("删除列: ").append(String.join(", ", removedColumns)).append(" [REMOVED]\n");
            }

            // 3. 比较索引差异
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> oldIndexes = (List<Map<String, Object>>) oldSnapshot.get("indexes");
            @SuppressWarnings("unchecked")
            List<Map<String, Object>> newIndexes = (List<Map<String, Object>>) newSnapshot.get("indexes");

            Set<String> oldIndexNames = oldIndexes.stream()
                    .map(idx -> (String) idx.get("indexName"))
                    .collect(Collectors.toSet());
            Set<String> newIndexNames = newIndexes.stream()
                    .map(idx -> (String) idx.get("indexName"))
                    .collect(Collectors.toSet());

            // 3.1 新增的索引
            Set<String> addedIndexes = new HashSet<>(newIndexNames);
            addedIndexes.removeAll(oldIndexNames);
            if (!addedIndexes.isEmpty()) {
                diff.append("新增索引: ").append(String.join(", ", addedIndexes)).append(" [ADDED]\n");
            }

            // 3.2 删除的索引
            Set<String> removedIndexes = new HashSet<>(oldIndexNames);
            removedIndexes.removeAll(newIndexNames);
            if (!removedIndexes.isEmpty()) {
                diff.append("删除索引: ").append(String.join(", ", removedIndexes)).append(" [REMOVED]\n");
            }

            return diff.toString();

        } catch (Exception e) {
            log.error("比较版本差异失败: {}", e.getMessage(), e);
            throw new RuntimeException("比较版本差异失败", e);
        }
    }

    /**
     * 删除旧版本(保留最新的N个版本)
     *
     * @param table          表
     * @param keepVersions   保留版本数(默认30)
     */
    @Transactional
    public void deleteOldVersions(MetadataTable table, int keepVersions) {
        log.info("清理旧版本: table={}, keepVersions={}", table.getFullName(), keepVersions);

        // 1. 获取当前版本总数
        long totalVersions = versionRepository.countByTable(table);

        // 2. 如果版本数小于等于保留数量,则不删除
        if (totalVersions <= keepVersions) {
            log.debug("版本数({})未超过保留限制({}),无需清理", totalVersions, keepVersions);
            return;
        }

        // 3. 获取所有版本(按版本号降序)
        List<MetadataVersion> versions = versionRepository.findByTableOrderByVersionNumberDesc(table);

        // 4. 计算要保留的最小版本号
        int minVersionToKeep = versions.get(keepVersions - 1).getVersionNumber();

        // 5. 删除低于最小版本号的所有版本
        int deletedCount = versionRepository.deleteByTableAndVersionNumberLessThan(table, minVersionToKeep);
        log.info("清理完成: table={}, deletedCount={}", table.getFullName(), deletedCount);
    }

    /**
     * 获取表的版本历史
     *
     * @param table 表
     * @return 版本列表(按版本号降序)
     */
    public List<MetadataVersion> getVersionHistory(MetadataTable table) {
        return versionRepository.findByTableOrderByVersionNumberDesc(table);
    }

    /**
     * 获取表的最新版本
     *
     * @param table 表
     * @return 最新版本Optional
     */
    public Optional<MetadataVersion> getLatestVersion(MetadataTable table) {
        return versionRepository.findFirstByTableOrderByVersionNumberDesc(table);
    }

    /**
     * 生成版本快照(JSONB格式)
     *
     * @param table   表
     * @param columns 列
     * @param indexes 索引
     * @return JSONB字符串
     */
    private String generateVersionSnapshot(MetadataTable table, List<MetadataColumn> columns, List<MetadataIndex> indexes) {
        try {
            Map<String, Object> snapshot = new HashMap<>();

            // 表基本信息
            snapshot.put("tableSchema", table.getTableSchema());
            snapshot.put("tableName", table.getTableName());
            snapshot.put("tableType", table.getTableType());
            snapshot.put("rowCount", table.getRowCount());

            // 列信息
            List<Map<String, Object>> columnList = columns.stream()
                    .map(col -> {
                        Map<String, Object> colMap = new HashMap<>();
                        colMap.put("columnName", col.getColumnName());
                        colMap.put("dataType", col.getDataType());
                        colMap.put("nullable", col.getNullable());
                        colMap.put("isPrimaryKey", col.getIsPrimaryKey());
                        colMap.put("columnPosition", col.getColumnPosition());
                        return colMap;
                    })
                    .collect(Collectors.toList());
            snapshot.put("columns", columnList);

            // 索引信息
            List<Map<String, Object>> indexList = indexes.stream()
                    .map(idx -> {
                        Map<String, Object> idxMap = new HashMap<>();
                        idxMap.put("indexName", idx.getIndexName());
                        idxMap.put("indexType", idx.getIndexType());
                        idxMap.put("columnNames", idx.getColumnNames());
                        idxMap.put("isUnique", idx.getIsUnique());
                        return idxMap;
                    })
                    .collect(Collectors.toList());
            snapshot.put("indexes", indexList);

            return objectMapper.writeValueAsString(snapshot);

        } catch (Exception e) {
            log.error("生成版本快照失败: {}", e.getMessage(), e);
            throw new RuntimeException("生成版本快照失败", e);
        }
    }

    /**
     * 提取表的特征集合(用于Jaccard相似度计算)
     *
     * @param columns 列列表
     * @param indexes 索引列表
     * @return 特征集合
     */
    private Set<String> extractFeatures(List<MetadataColumn> columns, List<MetadataIndex> indexes) {
        Set<String> features = new HashSet<>();

        // 添加列特征: 列名+数据类型
        columns.forEach(col -> {
            features.add("COL:" + col.getColumnName() + ":" + col.getDataType());
        });

        // 添加索引特征: 索引名(索引名通常包含业务含义)
        indexes.forEach(idx -> {
            features.add("IDX:" + idx.getIndexName());
        });

        return features;
    }

    /**
     * 将JSON字符串解析为Map
     *
     * @param jsonString JSON字符串
     * @return Map对象
     */
    private Map<String, Object> parseSnapshotToMap(String jsonString) {
        try {
            @SuppressWarnings("unchecked")
            Map<String, Object> map = objectMapper.readValue(jsonString, Map.class);
            return map;
        } catch (Exception e) {
            log.error("解析版本快照失败: {}", e.getMessage(), e);
            throw new RuntimeException("解析版本快照失败", e);
        }
    }

    /**
     * 参数校验
     *
     * @param table      表
     * @param changeType 变更类型
     */
    private void validateParameters(MetadataTable table, ChangeType changeType) {
        if (table == null) {
            throw new IllegalArgumentException("表不能为空");
        }
        if (changeType == null) {
            throw new IllegalArgumentException("变更类型不能为空");
        }
    }
}
